﻿using System;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;

namespace SSCCMembership.Web.Services.Members
{
    [ErrorHandlerBehavior]
    [ServiceContract(Namespace = "")]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class MembershipDetailsService
    {
        const string Query =
                "SELECT        SSCC_DAT_Member.FirstName, SSCC_DAT_Member.Surname, SSCC_DAT_Member.DoB, SSCC_DAT_Membership.MembershipTypeRC, " +
                "         SSCC_DAT_Membership.BCUCardReqd, SSCC_DAT_Membership.BCUNumber, SSCC_DAT_Membership.BCURenewalDate, SSCC_DAT_Year.StartDate, SSCC_DAT_Year.Year, " +
                "         SSCC_DAT_Year.MembershipDueDate " +
                "FROM            SSCC_DAT_Member INNER JOIN " +
                "         SSCC_DAT_Membership ON SSCC_DAT_Member.ID = SSCC_DAT_Membership.Member INNER JOIN " +
                "         SSCC_DAT_Year ON SSCC_DAT_Membership.Year = SSCC_DAT_Year.ID " +
                "WHERE        (SSCC_DAT_Member.ID = @ID) AND (SSCC_DAT_Year.StartDate < @NOW) AND (SSCC_DAT_Year.EndDate > @NOW) ";
        [OperationContract]
        public MembershipDetailsData GetMembershipDetails(int MemberID)
        {
            SecurityTester.UserCanEditMember(MemberID);
            var MDD = new MembershipDetailsData();
            MDD.MemberID = MemberID;
            using (var Q = SSCCDatabase.Query(Query,new Dictionary<SqlParameter,object>(){
                {new SqlParameter("@ID", SqlDbType.Int), MemberID},
                {new SqlParameter("@NOW", SqlDbType.Date), DateTime.Now}
            }))
            {
                //ErrorHandlerBehavior.Log("CreatedQuery");
                var M = Q.First();
                //ErrorHandlerBehavior.Log("ExecutedQuery");
                MDD.Year = (int)M["Year"];
                MDD.RenewalDue = M["MembershipDueDate"].Nullable<DateTime?>();
                MDD.FullName = String.Format("{0} {1}", M["FirstName"], M["Surname"]);
                MDD.MembershipType = M["MembershipTypeRC"].Nullable<int?>();
                MDD.BCUCardReqd = M["BCUCardReqd"].Nullable<bool?>();
                MDD.BCUNumber = M["BCUNumber"].ToString();
                MDD.BCURenewalDate = M["BCURenewalDate"].Nullable<DateTime?>();

                //ErrorHandlerBehavior.Log("Up To Calculate Age");
                var SD = M["StartDate"].Nullable<DateTime?>();
                var DoB = M["DoB"].Nullable<DateTime?>();

                if (SD.HasValue && DoB.HasValue)
                {
                    //http://stackoverflow.com/questions/9/how-do-i-calculate-someones-age-in-c/11942#11942
                    var CurrentDate = int.Parse(SD.Value.ToString("yyyyMMdd"));
                    var DOB = int.Parse(DoB.Value.ToString("yyyyMMdd"));
                    var Age = (CurrentDate - DOB) / 10000;//Ignore Month and Day Properties.

                    MDD.AgeAtStartOfYear = Age;
                }
                //ErrorHandlerBehavior.Log("CalculatedAge");
            }

            return MDD;
        }


        const string QurryMembershipID =
            "SELECT        SSCC_DAT_Membership.ID " +
            "FROM            SSCC_DAT_Member INNER JOIN " +
            "             SSCC_DAT_Membership ON SSCC_DAT_Member.ID = SSCC_DAT_Membership.Member INNER JOIN " +
            "             SSCC_DAT_Year ON SSCC_DAT_Membership.Year = SSCC_DAT_Year.ID " +
            "WHERE        (SSCC_DAT_Member.ID = @ID) AND (SSCC_DAT_Year.StartDate < @NOW) AND (SSCC_DAT_Year.EndDate > @NOW)";
        [OperationContract]
        public void Update(int MemberID, string PropertyName, object PropertyValue)
        {
            SecurityTester.UserCanEditMember(MemberID);

            int MembershipID = 0;
            using (var Q = SSCCDatabase.Query(QurryMembershipID, new Dictionary<SqlParameter, object>(){
                {new SqlParameter("@ID", SqlDbType.Int), MemberID},
                {new SqlParameter("@NOW", SqlDbType.Date), DateTime.Now}
            }))
            {
                MembershipID = (int)Q.First()["ID"];
            }

            SqlParameter Param = new SqlParameter();
            switch (PropertyName)
            {
                case "MembershipTypeRC":
                    Param = new SqlParameter("@value", SqlDbType.Int);
                    break;
                case "BCUCardReqd":
                    Param = new SqlParameter("@value", SqlDbType.Bit);
                    break;
               case "BCUNumber":
                    Param = new SqlParameter("@value", SqlDbType.VarChar);
                    break;
                case "BCURenewalDate":
                    Param = new SqlParameter("@value", SqlDbType.Date);
                    break;
                default: 
                    return;
            }

            SSCCDatabase.ExecuteStatement("UPDATE SSCC_DAT_Membership SET " + PropertyName + "=@value WHERE ID=@ID"
                , new Dictionary<SqlParameter, object>()
                {
                    {Param, PropertyValue.ToDBNullable()},
                    {new SqlParameter("@ID", SqlDbType.Int), MembershipID}
                });
        }
    }


    public class MembershipDetailsData
    {
        public int MemberID { get; set; }

        public int Year { get; set; }
        public string FullName { get; set; }
        public DateTime? RenewalDue { get; set; }

        public int? AgeAtStartOfYear { get; set; }
        public int? MembershipType { get; set; }

        public bool? BCUCardReqd { get; set; }
        public string BCUNumber { get; set; }
        public DateTime? BCURenewalDate { get; set; }
    }
}
